package com.bjsdzk.common.excel;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import com.bjsdzk.common.core.ServiceException;
import org.apache.commons.lang3.ObjectUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.util.*;

//Excel导入导出工具类
public class ExcelUtils {
    public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName,
                                   boolean isCreateHeader, HttpServletResponse response) {
        ExportParams exportParams = new ExportParams(title, sheetName);
        exportParams.setCreateHeadRows(isCreateHeader);
        defaultExport(list, pojoClass, fileName, response, exportParams);
    }

    public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName,
                                   HttpServletResponse response) {
        defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName));
    }

    public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response) {
        defaultExport(list, fileName, response);
    }

    private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response,
                                      ExportParams exportParams) {
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
        if (workbook != null)
            ;
        downLoadExcel(fileName, response, workbook);
    }

    private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) {
        try {
            response.setCharacterEncoding("UTF-8");
            response.setHeader("content-Type", "application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
            workbook.write(response.getOutputStream());
        } catch (IOException e) {
            // throw new NormalException(e.getMessage());
        }
    }

    private static void defaultExport(List<Map<String, Object>> list, String fileName, HttpServletResponse response) {
        Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF);
        if (workbook != null)
            ;
        downLoadExcel(fileName, response, workbook);
    }

    public static <T> List<T> importExcel(String filePath, Integer titleRows, Integer headerRows, Class<T> pojoClass) {
        if (StringUtils.isBlank(filePath)) {
            return null;
        }
        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        List<T> list = null;
        try {
            list = ExcelImportUtil.importExcel(new File(filePath), pojoClass, params);
        } catch (NoSuchElementException e) {
            // throw new NormalException("模板不能为空");
        } catch (Exception e) {
            e.printStackTrace();
            // throw new NormalException(e.getMessage());
        }
        return list;
    }

    public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows,
                                          Integer sheetStart,
                                          Class<T> pojoClass) {
        if (file == null) {
            return null;
        }
        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        params.setStartSheetIndex(sheetStart);
        List<T> list = null;
        try {
            list = ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params);
        } catch (NoSuchElementException e) {
            e.printStackTrace();
            System.out.println("====报错111");
            // throw new NormalException("excel文件不能为空");
        } catch (Exception e) {
            // throw new NormalException(e.getMessage());
            e.printStackTrace();
            System.out.println("====报错222");
        }
        return list;
    }

    /**
     * @Description : 根据字段名给对象的字段赋值
     * @param fieldName 字段名
     * @param fieldValue 字段值
     * @param o 对象
     */
    private static void setFieldValueByName(String fieldName, Object fieldValue, Object o) throws Exception {

        Field field = getFieldByName(fieldName, o.getClass());
        if (field != null) {
            field.setAccessible(true);
            // 获取字段类型
            Class<?> fieldType = field.getType();

            //增加判断是空串时不给属性赋值
            String str = ObjectUtils.toString(fieldValue, "");
            if(StringUtils.isBlank(str)){
                //当前的字段是""
                field.set(o, null);
            }else {
                // 根据字段类型给字段赋值
                if (String.class == fieldType) {
                    field.set(o, String.valueOf(fieldValue));
                } else if ((Integer.TYPE == fieldType)
                        || (Integer.class == fieldType)) {
                    field.set(o, Integer.parseInt(fieldValue.toString()));
                } else if ((Long.TYPE == fieldType) || (Long.class == fieldType)) {
                    field.set(o, Long.valueOf(fieldValue.toString()));
                } else if ((Float.TYPE == fieldType) || (Float.class == fieldType)) {
                    field.set(o, Float.valueOf(fieldValue.toString()));
                } else if ((Short.TYPE == fieldType) || (Short.class == fieldType)) {
                    field.set(o, Short.valueOf(fieldValue.toString()));
                } else if ((Double.TYPE == fieldType)
                        || (Double.class == fieldType)) {
                    field.set(o, Double.valueOf(fieldValue.toString()));
                } else if (Character.TYPE == fieldType) {
                    if ((fieldValue != null)
                            && (fieldValue.toString().length() > 0)) {
                        field.set(o,
                                Character.valueOf(fieldValue.toString().charAt(0)));
                    }
                } /*else if (Date.class == fieldType) {

                    field.set(o, DateUtils.parseDate(fieldValue.toString(), DatePtn));

                }*/ else {
                    field.set(o, fieldValue);
                }
            }
        }
    }

    /**
     * @Description : 根据字段名获取字段
     * @param fieldName  字段名
     * @param clazz 包含该字段的类
     * @return 字段
     */
    private static Field getFieldByName(String fieldName, Class<?> clazz) {
        // 拿到本类的所有字段
        Field[] selfFields = clazz.getDeclaredFields();

        // 如果本类中存在该字段，则返回
        for (Field field : selfFields) {
            if (field.getName().equals(fieldName)) {
                return field;
            }
        }

        // 否则，查看父类中是否存在此字段，如果有则返回
        Class<?> superClazz = clazz.getSuperclass();
        if (superClazz != null && superClazz != Object.class) {
            return getFieldByName(fieldName, superClazz);
        }

        // 如果本类和父类都没有，则返回空
        return null;
    }

    /**
     * 根据表头字母获取excel表头对应的列号,从0开始
     * @param headChars 如：A、B、C、AA、AB、AC
     * @return
     */
    private static int getExcelColumnIndex(String headChars){

        if(headChars == null || !headChars.matches("[a-zA-Z]+")){
            throw new IllegalArgumentException("invalid key " + headChars + ",key must be 26 letters, for example: A、B、C...AA、AB、AC etc.");
        }
        if(headChars.length() > 2){
            throw new IllegalArgumentException("key " + headChars + " length is too long must <=2");
        }
        headChars = headChars.toUpperCase(); // 转为大写字母
        char char0 = headChars.charAt(0);

        if(headChars.length() == 1){ //长度为1时
            return (int)char0 - 65;
        }
        char char1 = headChars.charAt(1); //长度为2时
        return ((int)char0 - 64) * 26 + (int)char1 - 65;
    }

    /**
     * @param entityClass List中对象的类型（Excel中的每一行都要转化为该类型的对象）
     * @param fieldMap    Excel中的中文列头和类的英文属性的对应关系Map,key：取值为A、B、C、D...AA、AB、AC，value：对应bean的属性名称
     * @param start       开始行数
     *                    属性名称接受带路径的属性名，如student.department.name等
     * @return
     */
    public static <T> List<T> excelToList(InputStream inputStream, Class<T> entityClass, LinkedHashMap<String, String> fieldMap, int start) throws Exception {
        //将文件读入
        XSSFWorkbook wb = new XSSFWorkbook(inputStream);

        if (entityClass == null) {
            throw new IllegalArgumentException("entityClass must not be null or empty");
        }
        if (fieldMap == null || fieldMap.isEmpty()) {
            throw new IllegalArgumentException("fieldMap must not be null or empty");
        }
        HashMap<String, Integer> letter2ColMap = new HashMap<String, Integer>(); //列名与列index的对应关系
        for (Map.Entry<String, String> entry : fieldMap.entrySet()) {
            String cnNormalName = entry.getKey();
            int index = getExcelColumnIndex(cnNormalName);
            letter2ColMap.put(cnNormalName, index);
        }

        // 定义要返回的list
        List<T> resultList = new ArrayList<T>();

        try {

            for (int i = 0; i < wb.getNumberOfSheets(); i++) {
                Sheet sheet = wb.getSheetAt(i); // 第i个sheet

                // 将sheet转换为list
                for (int r = start; r <= sheet.getLastRowNum(); r++) {
                    Row row = sheet.getRow(r);
                    T entity = entityClass.newInstance();

                    for (Map.Entry<String, String> entry : fieldMap.entrySet()) {

                        // 获取bean 属性名称
                        String enNormalName = entry.getValue();

                        // 根据ABC获取列号
                        int col = letter2ColMap.get(entry.getKey());

                        // 获取当前单元格中的内容
                        String content = null;
                        Cell cell = row.getCell(col);
                        System.out.println(row.getCell(col));
                        try {
                            content = cell.toString().trim();
                        } catch (Exception e) {
                            throw new ServiceException("error occurs on sheet: " + sheet.getSheetName() + " ,position:" + entry.getKey() + col, e);
                        }

                        // 给对象赋值
                        try {
                            setFieldValueByName(enNormalName, content, entity);
                        } catch (Exception e) {
                            throw new ServiceException("error occurs on sheet: " + sheet.getSheetName() + " ,position:" + entry.getKey() + col, e);
                        }
                    }
                    resultList.add(entity);
                }
            }

        } catch (Exception e) {
            e.printStackTrace();
            throw new ServiceException(e.getMessage(), e);
        } finally {
            if (wb != null) wb.close();
        }
        return resultList;
    }

}